﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;


namespace DAO
{
    public class ManagerFileDAO
    {
        public static List<ManagerFileDTO> GetFileByID(String ID)
        {
            List<ManagerFileDTO> listFile = new List<ManagerFileDTO>();
            SqlConnection connection = new SqlConnection(MySqlConnection.Connection());
            connection.Open();
            SqlCommand command = new SqlCommand("SelectFileByID", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("ID", ID));

            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            foreach (DataRow dr in dt.Rows)
            {
                ManagerFileDTO fileDTO = new ManagerFileDTO();
                fileDTO.Id = dr["ID"].ToString();
                fileDTO.Name = dr["Name"].ToString();
                fileDTO.Size = float.Parse(dr["Size"].ToString());
                fileDTO.Extension = dr["Extension"].ToString();

                //System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
                //System.Text.UTF8Encoding encoding = new UTF8Encoding();
               
                //fileDTO.Data = encoding.GetBytes(dr["Data"].ToString());
                fileDTO.Data = (byte[])(dr["Data"]);

                //System.IO.MemoryStream memStream = new System.IO.MemoryStream();
                //System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(typeof(object[]));
                //serializer.Serialize(memStream, dr.ItemArray);

                //fileDTO.Data = memStream.ToArray();
                



                listFile.Add(fileDTO);
            }

            return listFile;

        }
        public static List<ManagerFileDTO> GetAllFile()
        {
            List<ManagerFileDTO> listFile = new List<ManagerFileDTO>();
            SqlConnection connection = new SqlConnection(MySqlConnection.Connection());
            connection.Open();
            SqlCommand command = new SqlCommand("SelectAllFile", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            foreach (DataRow dr in dt.Rows)
            {
                ManagerFileDTO fileDTO = new ManagerFileDTO();
                fileDTO.Id = dr["ID"].ToString();
                fileDTO.Name = dr["Name"].ToString();
                fileDTO.Size = float.Parse(dr["Size"].ToString());
                fileDTO.Extension = dr["Extension"].ToString();

                //System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
                ////System.Text.UTF8Encoding encoding = new UTF8Encoding();

                //fileDTO.Data = encoding.GetBytes(dr["Data"].ToString());
                fileDTO.Data = (byte[])(dr["Data"]);

                //System.IO.MemoryStream memStream = new System.IO.MemoryStream();
                //System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(typeof(object[]));
                //serializer.Serialize(memStream, dr.ItemArray);

                //fileDTO.Data = memStream.ToArray();

   
                listFile.Add(fileDTO);
            }

            return listFile;
        }

        public static DataSet GetDataSet2XML2()
        {
            //SqlConnection connection = new SqlConnection(MySqlConnection.Connection());
            //connection.Open();
            //SqlCommand command = new SqlCommand("SelectAllFile", connection);

            //SqlDataAdapter adapter = new SqlDataAdapter();
            //adapter.SelectCommand = command;
            //DataSet ds = new DataSet();
            //adapter.Fill(ds);
            //connection.Close();

            DataSet ds = new DataSet();

            List<ManagerFileDTO> tempList = new List<ManagerFileDTO>();
            tempList = ManagerFileDAO.GetAllFile();

            DataTable dt = new DataTable();

            foreach (PropertyInfo info in typeof(ManagerFileDTO).GetProperties())
            {
                dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));
            }
            foreach (ManagerFileDTO t in tempList)
            {
                DataRow row = dt.NewRow();
                foreach (PropertyInfo info in typeof(ManagerFileDTO).GetProperties())
                {
                    row[info.Name] = info.GetValue(t, null);
                }
                dt.Rows.Add(row);
            }
            ds.Tables.Add(dt);
            
            return ds;
        }
        public static bool SaveFile(ManagerFileDTO file)
        {
            List<SqlParameter> listSqlParam = new List<SqlParameter>();
            listSqlParam.Add(new SqlParameter("@ID", file.Id));
            listSqlParam.Add(new SqlParameter("@Name", file.Name));
            listSqlParam.Add(new SqlParameter("@Size", file.Size));
            listSqlParam.Add(new SqlParameter("@Extension", file.Extension));


            SqlParameter pram1 = new SqlParameter("@Data", SqlDbType.Binary);
            pram1.Value = file.Data;

            listSqlParam.Add(pram1);


            SqlConnection connection = new SqlConnection(MySqlConnection.Connection());
            connection.Open();
            SqlCommand command = new SqlCommand("InsertFile", connection);
            command.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter temp in listSqlParam)
            {
                command.Parameters.Add(temp);
            }
            command.ExecuteNonQuery();
            connection.Close();

            return true;

        }
    }
}
